go

exec dbo.p_drop_func_if_exists 'p_generate_order_index';

go

--Function that generates new column order index
create function p_generate_order_index(
    --Table id
    @a_table_id binary(16))
    returns numeric(5)
as
begin
	declare @a_order_index numeric(5);
	
    select @a_order_index = max(order_index) + 1
    from meta_table_columns
    where table_id = @a_table_id;
    
	if @a_order_index is null
		set @a_order_index = 0;
    
    return @a_order_index;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_not_exists';

go

--Function of checking that column with the specified id and name doesn't exist
create procedure p_check_col_not_exists(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;    
    
    --Searching for the column id
    select @a_row_count = count(*)
    from meta_table_columns
    where column_id = @a_column_id;
    
    if @a_row_count > 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		
        raiserror('A column with the specified id was already added (column_id = %s) (#error_code = 20020)',
        16, 1, @a_column_id_str);
        set @a_result = 0;
        return;
    end;
    
    --Searching for the column name
    select @a_row_count = count(*)
    from meta_table_columns
    where table_id = @a_table_id and column_name = @a_column_name;
    
    if @a_row_count > 0
    begin
        raiserror('A column with the specified name was already added (column_name = %s) (#error_code = 20021)',
        16, 1, @a_column_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_string_column';

go

--Function of adding string column
create procedure meta_add_string_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id,
    @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    set nocount on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_string_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values(@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;

    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' nvarchar(2000)');
        
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;   
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_exists';

go

--Function of checking column existence
create procedure p_check_col_exists(
    --Column id
    @a_column_id binary(16),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;
    
    --Searching for the column
    select @a_row_count = count(*)
    from meta_table_columns
    where column_id = @a_column_id;
    
    if @a_row_count = 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
        raiserror('Specified column was not found (column_id = %s) (#error_code = 20019)',
        16, 1, @a_column_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_type';

go

--Function of checking column type
create procedure p_check_col_type(
    --Column id
    @a_column_id binary(16),
    --Column table name
    @a_column_table_name varchar(26),
    --Column type name
    @a_column_type_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;
    
	declare @check_col_existence_sql nvarchar(500);
	set @check_col_existence_sql = 'select @row_count_out = count(*) from ' + @a_column_table_name
		+ ' where column_id = @col_id';
    
    exec sp_executesql @check_col_existence_sql,
		N'@col_id binary(16), @row_count_out int output', 
		@col_id = @a_column_id,
		@row_count_out = @a_row_count output;
    
    if @a_row_count = 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		declare @a_err_message varchar(500);
		set @a_err_message = 'Specified column (of type '
                + @a_column_type_name
                + ') was not found (column_id = %s) (#error_code = 20019)';
                
        raiserror(@a_err_message, 16, 1, @a_column_id_str);
        
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_col_constraints';

go

--Function of dropping column constraints
create procedure p_drop_col_constraints(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Constraint cursor
	@a_constraint_cursor cursor,
	--Column name
	@a_column_name varchar(26),
	--Constraint name
	@a_constraint_name varchar(255),
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26);
	
	select @a_column_name = column_name,
	@a_table_id = table_id
	from meta_table_columns
	where column_id = @a_column_id;
	
	set @a_table_name = dbo.p_get_table_name(@a_table_id);
	
	--Dropping usual constraints
	set @a_constraint_cursor = cursor fast_forward read_only for
	select constraint_name
	from information_schema.constraint_column_usage
	where column_name = @a_column_name
	and table_name = 'tbl_' + @a_table_name;
	
	open @a_constraint_cursor;
	fetch next from @a_constraint_cursor into @a_constraint_name;
	
	while @@fetch_status = 0
	begin
		exec ('alter table tbl_' + @a_table_name
		+ ' drop constraint ' + @a_constraint_name);
		
		fetch next from @a_constraint_cursor into @a_constraint_name;
	end;
	
	close @a_constraint_cursor;
	deallocate @a_constraint_cursor;
	
	--Dropping default constraints
	set @a_constraint_name = null;
	
	select @a_constraint_name = name 
	from sys.default_constraints 
	where parent_object_id = object_id('tbl_' + @a_table_name)
	and type = 'D'
	and parent_column_id = (
		select column_id 
		from sys.columns 
		where object_id = object_id('tbl_' + @a_table_name)
		and name = @a_column_name);
		
	if @a_constraint_name is not null
		exec ('alter table tbl_' + @a_table_name
		+ ' drop constraint ' + @a_constraint_name);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_column';

go

--Function of removing string column
create procedure meta_delete_column(
    --Column id
    @a_column_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Table name
    @a_table_name varchar(26),
    --Table column name
    @a_column_table_name varchar(30),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);

    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_table_name = table_name,
    @a_column_table_name = column_type, @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Deleting system column is forbidden (column_id = %s) (#error_code = 20005)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting column constraints(if any)
    exec dbo.p_drop_col_constraints @a_column_id;
    
    --Deleting column indexes (if any)
    exec dbo.p_delete_column_indexes @a_column_id;
    
    --If this is ref column
    if @a_column_table_name like '%meta_ref_columns'
        --Dropping ref checking trigger
        exec dbo.p_drop_ref_checking @a_column_id;
    
    --Updating the metadata
    exec ('delete from ' + @a_column_table_name
    + ' where column_id = 0x' + @a_column_id_str);
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    exec ('alter table tbl_'
    + @a_table_name
    + ' drop column ' + @a_column_name);
    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_rename_column';

go

--Function of renaming string column
create procedure meta_rename_column(
    --Column id
    @a_column_id binary(16),
    --New column name
    @a_new_column_name varchar(max))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Old column name
    @a_old_column_name varchar(26),
    --Column renaming feature
    @column_renaming_is_enabled numeric(1),
    --Column table name
    @a_column_table_name varchar(30),
    --Customization level
    @a_customization_level numeric(10),
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_old_column_name = column_name,
    @a_table_name = table_name,
    @a_column_table_name = column_type, @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Searching for the column with duplicate name
    select @a_row_count = count(*)
    from meta_table_columns
    where table_id = @a_table_id and column_name = @a_new_column_name;
    
    if @a_row_count > 0
    begin
        raiserror('A column with the specified name was already added (column_name = %s) (#error_code = 20021)',
        16, 1, @a_new_column_name);
        return;
    end;

    --If there is no need for renaming
    if @a_old_column_name = @a_new_column_name
        return;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Renaming system column is forbidden (column_id = %s) (#error_code = 20006)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --Checking column renaming
    select @column_renaming_is_enabled = is_enabled
    from meta_dbms_features
    where feature_name = 'COLUMN_RENAMING';
    
    --If feature is enabled
    if @column_renaming_is_enabled = 1
    begin
		set xact_abort on;
		begin tran;
      
        --Changing the metadata
        exec ('update ' + @a_column_table_name
        + ' set column_name = ''' + @a_new_column_name + ''''
        + ' where column_id = 0x' + @a_column_id_str);
        
        --Dropping table view
        exec dbo.p_drop_view_if_exists @a_table_name;
        
        --Droppping ALL column constraints (otherwise - it's impossible to rename)
        --If this is ref column
        if @a_column_table_name like '%meta_ref_columns'
            exec dbo.p_drop_ref_col_constrs @a_column_id;
		--If this is enum column
		else if @a_column_table_name like '%meta_enum_columns'
			exec dbo.p_drop_enum_col_constrs @a_column_id;
		--If this decimal column
		else if @a_column_table_name like '%meta_decimal_columns'
			exec dbo.p_drop_decimal_col_constrs @a_column_id;
        --If this is bool column
        else if @a_column_table_name like '%meta_bool_columns'
			exec dbo.p_drop_bool_col_constrs @a_column_id;
		--If this is int column
		else if @a_column_table_name like '%meta_int_columns'
			exec dbo.p_drop_int_col_constrs @a_column_id;
		--If this is small int column
		else if @a_column_table_name like '%meta_small_int_columns'
			exec dbo.p_drop_small_int_col_constrs @a_column_id;
		--If this is big int column
		else if @a_column_table_name like '%meta_big_int_columns'
			exec dbo.p_drop_big_int_col_constrs @a_column_id;
		--If this is datetime column
		else if @a_column_table_name like '%meta_datetime_columns'
			exec dbo.p_drop_datetime_col_constrs @a_column_id;
        
        --Object name
        declare @a_obj_name varchar(64);
        set @a_obj_name = 'tbl_' + @a_table_name + '.' + @a_old_column_name;
        
        exec sp_rename 
		@objname= @a_obj_name, 
		@newname = @a_new_column_name, 
		@objtype = 'COLUMN';
        
        --Restoring ALL column constraints
        --If this is ref column
        if @a_column_table_name like '%meta_ref_columns'
            --Creating ref checking trigger
            exec dbo.p_create_ref_col_constrs @a_column_id;
		--If this is enum column
		else if @a_column_table_name like '%meta_enum_columns'
			exec dbo.p_create_enum_col_constrs @a_column_id;
		--If this decimal column
		else if @a_column_table_name like '%meta_decimal_columns'
			exec dbo.p_create_decimal_col_constrs @a_column_id;
        --If this is bool column
        else if @a_column_table_name like '%meta_bool_columns'
			--Creating bool check
			exec dbo.p_create_bool_col_constrs @a_column_id;
		--If this is int column
		else if @a_column_table_name like '%meta_int_columns'
			exec dbo.p_create_int_col_constrs @a_column_id;
		--If this is small int column
		else if @a_column_table_name like '%meta_small_int_columns'
			exec dbo.p_create_small_int_col_constrs @a_column_id;
		--If this is big int column
		else if @a_column_table_name like '%meta_big_int_columns'
			exec dbo.p_create_big_int_col_constrs @a_column_id;
		--If this is datetime column
		else if @a_column_table_name like '%meta_datetime_columns'
			exec dbo.p_create_datetime_col_constrs @a_column_id;
			
		--Recreating column index triggers
        exec dbo.p_recreate_col_index_triggers @a_column_id;
        
        --Creating table view
        exec dbo.p_create_table_view @a_table_id;
        
        commit;
    end;        
    else
	begin
        raiserror('Renaming a column is not supported by the dbms (column_id = %s) (#error_code = 20007)',
        16, 1, @a_column_id_str);
		return;
    end;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_col_type';

go

--Function of getting column type
create function p_get_col_type(
	--Column id
	@a_column_id binary(16))
returns varchar(64)
as
begin
	declare
	--Column type str
	@a_col_type_str varchar(64),
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26),
	--Column name
	@a_column_name varchar(26),
	--Data type
	@a_data_type varchar(64),
	--Character maximum length
	@a_char_max_length varchar(64),
	--Numeric precision
	@a_num_precision varchar(5),
	--Numeric scale
	@a_num_scale varchar(5);	
	
	--Reading column properties
	select @a_table_id = table_id,
	@a_column_name = column_name,
	@a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	--Reading type properties
	select @a_data_type = data_type,
	@a_char_max_length = character_maximum_length,
	@a_num_precision = numeric_precision,
	@a_num_scale = numeric_scale
	from information_schema.columns
	where table_name = 'tbl_' + @a_table_name
	and column_name = @a_column_name;
	
	set @a_col_type_str = @a_data_type;
	
	-- If the maximum string length is specified
	if (@a_char_max_length is not null) and (@a_char_max_length <> '-1')
		set @a_col_type_str = @a_col_type_str + '(' + @a_char_max_length + ')';
	-- If it is a binary max column
	else if (@a_col_type_str = 'varbinary' or @a_col_type_str = 'nvarchar')
	 and @a_char_max_length = '-1'
		set @a_col_type_str = @a_col_type_str + '(max)';
	-- If the decimal precision and scale are specified
	else if @a_num_precision is not null and @a_num_scale is not null
		set @a_col_type_str = @a_col_type_str + '(' + @a_num_precision + ','
		+ @a_num_scale + ')';
	
	return @a_col_type_str;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_column_indexes';

go

--Function that recreates column indexes
create procedure p_recreate_column_indexes(
	--Column id
    @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16);
    
    --Reading column table name
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
	--Recreating the column indexes
	declare @index_cursor cursor;
	set @index_cursor = cursor fast_forward read_only for
	select index_id, is_unique, column_id_list
	from ##column_indexes;
    
    declare @a_index_id binary(16),
			@a_is_unique numeric(1),
			@a_column_id_list varbinary(256);
			
	open @index_cursor;
	fetch next from @index_cursor into @a_index_id, @a_is_unique, @a_column_id_list;
	
	while @@fetch_status = 0
	begin
		exec dbo.meta_add_index @a_index_id, @a_table_id, @a_is_unique, @a_column_id_list;
		
		fetch next from @index_cursor into @a_index_id, @a_is_unique, @a_column_id_list;
	end;
	
	close @index_cursor;
	deallocate @index_cursor;
	
	delete from ##column_indexes;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_column_nullability';

go

--Function of setting column nullability
create procedure meta_set_column_nullability(
    --Column id
    @a_column_id binary(16),
    --Is not null
    @a_is_not_null numeric(1))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Backend table name
    @a_column_table_name varchar(30),
    --Column name
    @a_column_name varchar(26),
    --Old is_not_null
    @a_old_is_not_null numeric(1),
    --Table name
    @a_table_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column table name
    select @a_column_table_name = column_type,
    @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing system column nullability is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --Reading column properties
    declare @read_col_props_sql nvarchar(500);
	set @read_col_props_sql	= 'select @a_table_id_out = table_id, @a_is_not_null_out = is_not_null,'
		+ ' @a_column_name_out = column_name from ' + @a_column_table_name
		+ ' where column_id = @col_id';
    
    exec sp_executesql @read_col_props_sql,
		N'@col_id binary(16), @a_table_id_out binary(16) output, @a_is_not_null_out numeric(1) output, @a_column_name_out varchar(26) output', 
		@col_id = @a_column_id,
		@a_table_id_out = @a_table_id output,
		@a_is_not_null_out = @a_old_is_not_null output,
		@a_column_name_out = @a_column_name output;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    if @a_is_not_null = @a_old_is_not_null
        return;
    
    set xact_abort on;
    begin tran;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    declare @a_column_type_str varchar(64);
    set @a_column_type_str = dbo.p_get_col_type(@a_column_id);
    
    if @a_is_not_null = 1
        exec ('alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' ' +@a_column_type_str
        + ' not null');
    else
        exec ('alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' ' + @a_column_type_str
        + ' null');
    
    --Changing the metadata
    exec ('update ' + @a_column_table_name
    + ' set is_not_null = ' + @a_is_not_null
    + ' where column_id = 0x' + @a_column_id_str);
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_string_default';

go

--Function of setting column default value
create procedure meta_set_string_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value nvarchar(2000))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value nvarchar(2000);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_string_columns', 'string', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_string_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
         raiserror('Changing string system column default value is forbidden (column_id = %s) (#error_code = 20008)',
         16, 1, @a_column_id_str);
         return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
		--Dropping the old default value
		exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_string_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ''' + @a_default_value + ''' for ' + @a_column_name);        
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_string_max_length';

go

--Function of setting string column max length
create procedure meta_set_string_max_length(
    --Column id
    @a_column_id binary(16),
    --Max length
    @a_max_length integer)
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_string_columns', 'string', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level, @a_is_not_null = is_not_null
    from meta_string_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing string system column max length is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;

	declare @a_sql_str varchar(500);

    if @a_max_length is not null
        set @a_sql_str = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' nvarchar(' + cast(@a_max_length as varchar) + ')';
    else
        set @a_sql_str = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' nvarchar(2000)';
    
    if @a_is_not_null = 1
		set @a_sql_str = @a_sql_str + ' not null';
		
	exec (@a_sql_str);
	
	--Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
        
    --Updating the metadata
    update meta_string_columns
    set max_length = @a_max_length
    where column_id = @a_column_id;
    
    commit;
end;